Lab 7 · Choose the Rows to Keep

UniLearn's registry and LMS do not contain the same student IDs. The team needs several reports, each with a different answer to “Which source rows must appear?”

Reuse the Lab 6 Database

If you already ran lab6_7_setup.sql in Lab 6, keep that data. Otherwise, open the shared setup file supplied by your tutor in pgAdmin and run it once. Select lab6_7 in every new Query Tool connection.

Begin with the Join You Know

Inspect students and lms_roster: 22 registry IDs and 6 LMS IDs.
Run the INNER JOIN from Lab 6's matching rule.
Identify the four IDs in both sources.
Explain why this result cannot be a complete list from either source.

Five Report Requests

List shared accounts; keep every registry student; keep every LMS account; reconcile both sources; generate student–course options. You will write INNER, LEFT, RIGHT, FULL and CROSS JOIN queries for these requests.

✍️ Predict the Missing Context

S103 is registered but has no LMS account. S901 is imported into the LMS but is absent from the registry. Where would each belong in a complete report?

SET search_path TO lab6_7;

SELECT s.student_id AS registry_id, s.student_name,
       r.student_id AS lms_id, r.lms_name
FROM students AS s
INNER JOIN lms_roster AS r
  ON s.student_id = r.student_id
ORDER BY s.student_id;

Check the matched IDs

Four rows: S101, S102, S105 and S121. Each ID is unique in both tables, so each matched pair appears once. This report contains only shared accounts.

Uma (S121) has an LMS account even though she has no enrolments. An account match does not prove course participation.

Inspect Both Sources

The LMS roster is an unvalidated import. It intentionally has no foreign key to students, so S901 and S902 can exist only in that source.

SET search_path TO lab6_7;

SELECT student_id, student_name
FROM students
ORDER BY student_id;

SELECT student_id, lms_name
FROM lms_roster
ORDER BY student_id;

LMS ID	LMS name	In registry?
S101	Alice Wong	Yes
S102	Ben Lau	Yes
S105	Emma Li	Yes
S121	Uma Shah	Yes
S901	Imported Visitor A	No
S902	Imported Visitor B	No

The shared setup also contains 8 courses, 37 enrolments, 8 assignments and 30 submissions for later tasks. All records are fictional.

Read the Request Before Choosing a Join

Request	Required result
Shared accounts	Only ID pairs that match.
Registry coverage	Every registry student, even without an LMS account.
LMS import audit	Every imported LMS account, even without a registry match.
Reconcile both systems	Shared accounts and records found in either source alone.
Course planning	Every selected student paired with every selected course.

Lab 6 established how matching pairs form rows. In this lab, change the join type to control unmatched rows or generate all combinations, then check what each result row represents.